package com.sunyard.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

/**
 * ExcelOutput
 *
 * @author guangw.dang
 * @Date: 2022/5/11 15:21
 */
@Slf4j
public class ExportExcelUtil {

    /**
     * 显示的导出表的标题
     */
    private String title;
    /**
     * 导出表的列名
     */
    private String[] rowName;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    HttpServletResponse response;

    /**
     * 构造方法，传入要导出的数据
     *
     * @author: guangw.dang
     * @Date: 2022/5/9 19:41
     **/
    public ExportExcelUtil(String title, String[] rowName, List<Object[]> dataList) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
    }

    /**
     * getOutputStream
     *
     * @param request  request
     * @param response response
     * @param title    导出文件标题
     * @param flag     导出文件标识， true-文件导出，false-导入错误文件下载
     * @return OutputStream
     * @author: guangw.dang
     * @Date: 2022/5/25 17:08
     **/
    public static OutputStream getOutputStream(HttpServletRequest request, HttpServletResponse response, String title) throws IOException {
        OutputStream out = null;
        String agent = request.getHeader("User-Agent").toLowerCase();
        response.setContentType("application/vnd.ms-excel");
        String codedFileName = java.net.URLEncoder.encode(title, "UTF-8");
        String ieType = "firefox";
        if (agent.contains(ieType)) {
            response.setCharacterEncoding("utf-8");
            response.setHeader("content-disposition", "attachment;filename=" + new String(title.getBytes(), "ISO8859-1") + ".xls");
        } else {
            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        }
        out = response.getOutputStream();
        return out;
    }

    /**
     * 导出数据
     *
     * @author: guangw.dang
     * @Date: 2022/5/9 19:31
     **/
    public void export(OutputStream out) throws Exception {
        try (
                // 创建工作簿对象
                HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            // 创建工作表
            HSSFSheet sheet = workbook.createSheet(title);
            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);
            int maxColumnNum = rowName.length;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(0);
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < maxColumnNum; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            if (CollectionUtils.isEmpty(dataList)) {
                return;
            }
            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataList.get(i);
                //创建所需的行数（从第二行开始写数据）
                HSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < obj.length; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j);
                    if (StringUtils.isEmpty(obj[j])) {
                        cell.setCellValue("");
                    } else {
                        //设置单元格的值
                        cell.setCellValue(obj[j].toString());
                    }
                    //设置单元格样式
                    cell.setCellStyle(style);
                }
            }
            setCellWidth(sheet, maxColumnNum);
            workbook.write(out);
        } catch (Exception e) {
            log.error("导出Excel出现错误:", e);
        } finally {
            out.close();
        }

    }

    /**
     * 导出异常文件
     *
     * @param workbook
     * @param sheet
     * @param out
     * @param dataList [workbook, sheet, out, dataList]
     **/
    public static void exportErrorFile(HSSFWorkbook workbook, HSSFSheet sheet, int rowNum, OutputStream out, List<Object[]> dataList) throws IOException {
        try {
            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataList.get(i);
                //创建所需的行数（从第二行开始写数据）
                HSSFRow row = sheet.createRow(rowNum + i);
                for (int j = 0; j < obj.length; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j);
                    if (StringUtils.isEmpty(obj[j])) {
                        cell.setCellValue("");
                    } else {
                        //设置单元格的值
                        cell.setCellValue(obj[j].toString());
                    }
                    if (!StringUtils.isEmpty(obj[j])) {
                        int length = obj[j].toString().getBytes().length;
                        sheet.setColumnWidth(obj.length - 1, length * 2 * 256);
                    }
                }
            }
            workbook.write(out);
        } catch (IOException e) {
            log.error("导出错误Excel出现错误:", e);
        } finally {
            out.close();
        }
    }

    private static void setCellWidth(HSSFSheet sheet, int maxColumnNum) throws UnsupportedEncodingException {
        //让列宽随着导出的列长自动适应
        for (int columnNum = 0; columnNum <= maxColumnNum; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) == null) {
                    continue;
                }
                Cell currentCell = currentRow.getCell(columnNum);
                int length = currentCell.toString().getBytes("utf-8").length;
                if (columnWidth < length + 1) {
                    columnWidth = length + 1;
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 300);
        }
    }

    /**
     * 列头单元格样式
     *
     * @author: guangw.dang
     * @Date: 2022/5/9 19:30
     **/
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }

    /**
     * 列数据信息单元格样式
     *
     * @author: guangw.dang
     * @Date: 2022/5/9 19:31
     **/
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //在样式用应用设置的字体;  
        style.setFont(font);
        //设置自动换行; 
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }
}
